---
title: "Wages and Prices"
output: 
  flexdashboard::flex_dashboard:
    orientation: columns
    vertical_layout: fill
    css: styles.css
    source: embed
---

```{r setup, include=FALSE}
library(flexdashboard)
```

```{r warning=FALSE, message=FALSE}

library(tidyverse)
library(ggspatial)
library(ggplot2)
library(ggrepel)
library(rnaturalearth)
library(rnaturalearthdata)
library(maptools)
library(geofacet)
library(readr)
library(gganimate)
library(rgdal)
library(ggmap)
library(zoo)
library(DT)

world <- ne_countries(scale = "medium", returnclass = "sf")
RDSfolder = "./data_temp/map.objects/"
waterColor = "lightsteelblue2"

theme_set(theme_bw())

themeParameters <- theme(panel.background = element_rect(fill = waterColor),
          axis.title.y=element_blank(),
          axis.title.x=element_blank(),
          axis.text.x=element_blank(),
          axis.ticks.x=element_blank(),
          axis.text.y=element_blank(),
          axis.ticks.y=element_blank(),
          panel.grid.major = element_line(color = waterColor, linetype = "dotted", size = 0.5))

eu_xlim=c(-12,30)
eu_ylim=c(36,58)

```

```{r warning=FALSE, message=FALSE}

wide_nominal_wages <- read_delim("./files/nominal_wages.csv", delim=";")

long_nominal_wages <- wide_nominal_wages %>%
  gather(city, wages, Antwerp:Hamburg)

nominal_wages_ma <- long_nominal_wages  %>%
  group_by(city) %>%
  mutate(wages_ma = rollmean(wages, k = 15, fill = NA))

nominal_wages <- nominal_wages_ma %>%
  filter(year >= 1400 )

##########

wide_cp_index <- read_delim("./files/consumerprice_index.csv", delim=";")

long_cp_index <- wide_cp_index %>%
  gather(city, index, Antwerp:Hamburg)

cp_index_ma <- long_cp_index  %>%
  group_by(city) %>%
  mutate(index_ma = rollmean(index, k = 15, fill = NA))

cp_index <- cp_index_ma %>%
  filter(year >= 1400 )

##########

wide_real_wages <- read_delim("./files/real_wages.csv", delim=";")

long_real_wages <- wide_real_wages %>%
  gather(city, wages, Antwerp:Hamburg)

real_wages_ma <- long_real_wages  %>%
  group_by(city) %>%
  mutate(wages_ma = rollmean(wages, k = 15, fill = NA))

real_wages <- real_wages_ma %>%
  filter(year >= 1400) %>%
  filter (city != "Milan") %>% #wrong data
  filter (city != "Oxford") #wrong data

##########

wide_welfare_ratio <- read_delim("./files/welfare_ratio.csv", delim=";")

long_welfare_ratio <- wide_welfare_ratio %>%
  gather(city, ratio, Antwerp:Hamburg)
  
welfare_ratio_ma <- long_welfare_ratio  %>%
  group_by(city) %>%
  mutate(ratio_ma = rollmean(ratio, k = 15, fill = NA))

welfare_ratio <- welfare_ratio_ma %>%
  filter(year >= 1400)

##########

starting_grid <- nominal_wages %>%
  select(city) %>% unique 

#grid_design(data = starting_grid)

mygrid <- data.frame(
  name = c("Antwerp", "Gdansk", "Amsterdam", "London", "Warsaw", "Leipzig", "Paris", "Strasbourg", "Augsburg", "Munich", "Vienna", "Krakow", "Lwow", "Florence", "Naples", "Valencia", "Madrid", "Hamburg", "Milan", "Oxford"),
  row = c(2, 1, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 4, 5, 5, 5, 1, 4, 2),
  col = c(3, 7, 4, 2, 7, 5, 2, 3, 4, 5, 6, 7, 8, 5, 5, 2, 1, 4, 4, 1),
  code = c("Antwerp", "Gdansk", "Amsterdam", "London", "Warsaw", "Leipzig", "Paris", "Strasbourg", "Augsburg", "Munich", "Vienna", "Krakow", "Lwow", "Florence", "Naples", "Valencia", "Madrid", "Hamburg", "Milan", "Oxford"),
  stringsAsFactors = FALSE
)

```

Intro
=====================================



**Provenance of data**

The data I use here comes from a study by the economic historian Robert C. Allen called "The Great Divergence in European Wages and Prices from the Middle Ages to the First World War." They include nominal wages, consumer price indices, real wages and welfare ratios of 20 European cities from the late 13th century to the early 20th century.

I downloaded the data as an Excel spreadsheet from here: https://dataverse.harvard.edu/file.xhtml?persistentId=doi:10.7910/DVN/TMZGSF/GAFFOK&version=1.0

* The nominal wages in the data are from building craftsmen, which are the most recorded in price histories. These are daily wages, which have been converted to grams of silver by the author for comparison.

* In order to compare the purchasing power of nominal wages, a consumer price index is needed. For this purpose, the author created a basket of goods with 12 different items - e.g. for example bread (30% of spending), beer/wine (20%), meat (15%) etc. The prices for the consumer price index are averages for Strasbourg in 1745–1754.

* The real wage equals the nominal wage divided by the consumer price index. The real wage shows proportional changes and relative levels. It has no absolute interpretation.

* To make the results more meaningful, the author calculates welfare ratios in addition to the real wages. The welfare ratio is average annual earnings divided by the cost of a poverty line consumption bundle for a family. 

**Data quality**

* Nominal wages: The problem here is that often only part of the wages were paid in money and the rest in food or even housing.This was managed very differently, and not always noted in the account books. Therefore, a comparison is not always given.

* Consumer price index: The selection and weighting of the different items of the basket can only be based on very rough simplifications and assumptions. In addition, different cuisines and climates must be taken into account for a European comparison.

* Real wages: If the data for nominal wages and the consumer price index are not correct, real wages are not correct either.

* Welfare ratios: To calculate the welfare ratios, the author makes a series of assumptions: Number of working days per year (250), the size of a family (a man, a woman and two children), the spending on rent (5%) etc.

**Preparation of data**

* Splitting the Excel spreadsheet into 4 different CSV files
* Reorganization according to the principles of tidy data
* Deleting of obviously incorrect data
* Adding of a 15-year moving average to smooth out short-term fluctuations
* Filtering out all records before 1400 (because there are so few)




Visualizations {.storyboard}
=====================================

### Nominal wages {data-commentary-width=400}

```{r  warning=FALSE, message=FALSE}

ggplot(nominal_wages, aes(x = year, y = wages_ma)) +
  geom_line(color = "tomato2") +
  facet_geo(~ city, grid = mygrid, label = "name") +
  labs(title = "Nominal wages of building craftsmen, 1400-1913",
    x = "Year",
    y = "Grams of silver per day") +
    theme(axis.text.x = element_text(angle = 90, size = 7), axis.text.y = element_text(size = 7), strip.text.x = element_text(size = 7))

```

***
Text Text Text

### Consumer price index {data-commentary-width=400}

```{r  warning=FALSE, message=FALSE}

ggplot(cp_index, aes(x = year, y = index_ma)) +
  geom_line(color = "tomato2") +
  facet_geo(~ city, grid = mygrid, label = "name") +
  labs(title = "Consumer Price Index (Strasbourg, 1745-1754 = 100)",
    x = "Year",
    y = "") +
  theme(axis.text.x = element_text(angle = 90, size = 7), axis.text.y = element_text(size = 7), strip.text.x = element_text(size = 7))

```

***
Text Text Text

### Real wages {data-commentary-width=400}

```{r warning=FALSE, message=FALSE}

ggplot(real_wages, aes(x = year, y = wages_ma)) +
  geom_line(color = "tomato2") +
  facet_geo(~ city, grid = mygrid, label = "name") +
  labs(title = "Real wages of building craftsmen, 1400-1913",
    x = "Year",
    y = "") +
  theme(axis.text.x = element_text(angle = 90, size = 7), axis.text.y = element_text(size = 7), strip.text.x = element_text(size = 7))

```

***
Text Text Text

### Welfare ratios {data-commentary-width=400}

```{r warning=FALSE, message=FALSE}

ggplot(welfare_ratio, aes(x = year, y = ratio_ma)) +
  geom_line(color = "tomato2") +
  facet_geo(~ city, grid = mygrid, label = "name") +
  labs(title = "Welfare ratios of building craftsmen, 1400-1913",
    x = "Year",
    y = "") +
  theme(axis.text.x = element_text(angle = 90, size = 7), axis.text.y = element_text(size = 7), strip.text.x = element_text(size = 7))

```

***
Text Text Text

Changes {.storyboard}
=====================================

### Standard deviation of the welfare ratios {data-commentary-width=400}

```{r warning=FALSE, message=FALSE}

welfare_ratio_stat <- welfare_ratio %>%
  ungroup() %>%
  select(year,ratio) %>%
  filter(year >= 1500)

deviation <- aggregate(. ~ year, welfare_ratio_stat, function(x) c(sd = sd(x)))

deviation <- deviation %>%
  rename(sd = ratio) %>%
  mutate(sd_ma = rollmean(sd, k = 15, fill = NA))

ggplot(deviation, aes(x = year, y = sd_ma)) +
  geom_line(color = "tomato2") +
  labs(title = "Standard deviation of the welfare ratios, 1500-1913",
    x = "Year",
    y = "")

```

***
To accurately test the assumption that differences were smallest in the early 16th century, we calculate the standard deviation of the welfare ratios.

Here we can see that the standard deviation is lowest in 1525 at 0.3 and highest in 1900 at about 0.85. With this knowledge, we now determine the mean of the welfare ratios in 1525. And based on this value, we now calculate the annual percentage changes of the welfare ratios.

### Changes of the welfare ratios in percent {data-commentary-width=400}

```{r warning=FALSE, message=FALSE}

welfare_ratio_1525 <- welfare_ratio %>%
  filter(year == "1525") %>%
  filter(! is.na(ratio))

ratio_mean_1525 <- mean(welfare_ratio_1525$ratio)

welfare_ratio_perc <- welfare_ratio %>%
  mutate(percentage = (ratio / ratio_mean_1525 -1) * 100) %>%
  mutate(percentage_ma = rollmean(percentage, k = 15, fill = NA)) %>%
  filter(year >= 1500)

ggplot(welfare_ratio_perc, aes(x = year, y = percentage_ma)) +
  geom_ribbon(aes(ymin = pmin(percentage_ma, 0), ymax = 0), fill = "red3", col = "red3", alpha = 0.5, size = 0.3) +
  geom_ribbon(aes(ymin = 0, ymax = pmax(percentage_ma, 0)), fill = "green4", col = "green4", alpha = 0.5, size = 0.3) +
  geom_line(aes(y = 0), color = "gray20", size = 0.3) +
  facet_geo(~ city, grid = mygrid, label = "name") +
  labs(title = "Changes of the welfare ratios in percent, 1500-1913",
    subtitle = "Base: 1.83 at 1525",
    x = "Year",
    y = "") +
  theme(axis.text.x = element_text(angle = 90, size = 7), axis.text.y = element_text(size = 7), strip.text.x = element_text(size = 7))

```

***
Test Test Test

Maps {.storyboard}
=====================================

### Welfare ratios at 1525 {data-commentary-width=400}

```{r  warning=FALSE, message=FALSE}

#rivers <- readOGR("./data_temp/layer.riverData", "ne_50m_rivers_lake_centerlines")
#rivers_df <- fortify(rivers)

#saveRDS(rivers_df, paste0(RDSfolder,"rivers_df.rds"))

rivers_df <- readRDS(paste0(RDSfolder,"rivers_df.rds"))

riversData <- rivers_df

cities <- read_csv("./files/cities_coords.csv")

map <- ggplot(data = world) +
  geom_sf(fill="white", color="white") +
  coord_sf(xlim = eu_xlim, ylim = eu_ylim, expand = TRUE) +
  geom_path(data = riversData, aes(x = long, y = lat, group = group), color = waterColor, size = .1) +
  scale_radius(range = c(1, 10), limits = c(0.1, 5)) +
  labs(size = "Welfare ratios") +
  themeParameters

welfare_ratio_coords <- welfare_ratio %>%
  left_join(cities, by="city")

welfare_ratio_coords_1525 <- welfare_ratio_coords %>%
  filter(year == "1525") %>%
  filter(! is.na(ratio_ma))

map1525 <- map +
  geom_point(data = welfare_ratio_coords_1525, aes(x = lng, y = lat, size = ratio_ma), color = "tomato2") +
  geom_label_repel(data = welfare_ratio_coords_1525, aes(x=lng, y=lat, label = city), color = "black", size = 2.5, segment.size = 0.25)

map1525

```

***
In 1525, where the standard deviation is the lowest, we can see that the welfare ratio in almost all cities is close to 2.

### Welfare ratios at 1750 {data-commentary-width=400}

```{r  warning=FALSE}

welfare_ratio_coords_1750 <- welfare_ratio_coords  %>%
  filter(year=="1750") %>%
  filter(! is.na(ratio_ma))

map1750 <- map +
  geom_point(data = welfare_ratio_coords_1750, aes(x=lng, y=lat, color=ratio, size=ratio_ma), color="tomato2") +
  geom_label_repel(data = welfare_ratio_coords_1750, aes(x=lng, y=lat, label = city), color="black", size = 2.5, segment.size=0.25)

map1750

```

***
225 years later, the situation is quite different. The welfare ratio has fallen to about 1 in many cities, but in a few, like London, Antwerp or Amsterdam, it has increased very slightly.
London has the highest welfare ratio at 2.44, but in some cities, like Strasbourg, Munich or Lwow, the welfare ratio has even fallen below 1.

### Welfare ratios at 1850 {data-commentary-width=400}

```{r warning=FALSE}

welfare_ratio_coords_1850 <- welfare_ratio_coords  %>%
  filter(year=="1850") %>%
  filter(! is.na(ratio_ma))

map1850 <- map +
  geom_point(data = welfare_ratio_coords_1850, aes(x=lng, y=lat, color=ratio, size=ratio_ma), color="tomato2") +
  geom_label_repel(data = welfare_ratio_coords_1850, aes(x=lng, y=lat, label = city), color="black", size = 2.5, segment.size=0.25)

map1850

```

***
At 1850, unfortunately, there is no more data from many cities.
The welfare ratio is now highest in Warsaw at 3.26 and lowest in Florence at 0.71, and the differences have continued to grow.

### Welfare ratios at 1900 {data-commentary-width=400}

```{r warning=FALSE}

welfare_ratio_coords_1900 <- welfare_ratio_coords  %>%
  filter(year=="1900") %>%
  filter(! is.na(ratio_ma))

map1900 <- map +
  geom_point(data = welfare_ratio_coords_1900, aes(x=lng, y=lat, size=ratio_ma), color="tomato2") +
  geom_label_repel(data = welfare_ratio_coords_1900, aes(x=lng, y=lat, label = city), color="black", size = 2.5, segment.size = 0.25) 

map1900

```

***
At 1900, the differences are similar to those in 1850, with London again having the highest welfare ratio at 4.10 and Florence still the lowest at 1.43.


Data {.storyboard}
=====================================

### Nominal wages: `nominal_wages` {data-commentary-width=400}

```{r warning=FALSE}

datatable(nominal_wages, options = list(pageLength = 50))

```

### Consumer price index: `cp_index` {data-commentary-width=400}

```{r warning=FALSE}

datatable(cp_index, options = list(pageLength = 50))

```

### Real wages: `real_wages` {data-commentary-width=400}

```{r warning=FALSE}

datatable(real_wages, options = list(pageLength = 50))

```

### Welfare ratios: `welfare_ratio` {data-commentary-width=400}

```{r warning=FALSE}

datatable(welfare_ratio, options = list(pageLength = 50))

```